数据库参考# https://blog.csdn.net/liuyanlin610/article/details/76021959
'''导入sqlite3库'''
import sqlite3

'''创建数据库'''
conn = sqlite3.connect('demo.db')  # demo.db不存在时在py同一目录下自动创建demo.db

'''创建游标'''
cursor = conn.cursor()

'''建表'''
create_table_sql = '''create table IF NOT EXISTS happy(username text,password text,id int)'''

cursor.execute(create_table_sql)  # 执行这个语句

'''关闭连接'''
conn.commit()
cursor.close()

# 二、插入数据：执行语句即可
username = "ytouch"
password = "123456"
user_id = 1
insert_data_sql = '''insert into happy(username,password,id)values (:st_name,:st_username,:st_id)'''
cursor.execute(insert_data_sql, {'st_name': username, 'st_username': password, 'st_id': user_id})



# 三、修改数据：执行以下语句就可以
update_sql = 'update happy set password = ? where username = ?'
cursor.execute(update_sql,("4578623","ytouch"))

# 四、查询数据:执行以下语句就可以
'''查询语句'''
search_sql = "select username,password from happy"

'''执行语句'''
results = cursor.execute(search_sql)

'''遍历打印输出'''
all_results = results.fetchall()
for happys in all_results:
    print(type(happys))  # type:tuple
    print(happys)

# 五、删除数据
#
# 1.删除指定数据
delete_sql = 'delete from happy where id = 25'
# 2.删除一整个表格
delete_all_sql = 'drop table happy' #删除一整个表格